﻿--------------------------------------------------------------------------
-- Licensed under GNU General Public License version 2 (GPLv2)
-- TPL Helper Library by DHGMS Solutions
-- http://tplhelper.codeplex.com
--------------------------------------------------------------------------

DECLARE @databaseName AS VARCHAR(20) = DB_NAME(0)

DECLARE @missingIndexCreationLogic AS TABLE(
	QUERY VARCHAR(MAX)
)

INSERT INTO @missingIndexCreationLogic(QUERY)
SELECT
	'CREATE NONCLUSTERED INDEX [IX_' + TABS.name + '_' + CAST(INDEX_GROUPS.index_handle AS VARCHAR(10)) + '] ON [dbo].[' + TABS.name + ']'
	+ '('
	+ CASE WHEN INDEX_DETAILS.equality_columns IS NOT NULL THEN INDEX_DETAILS.equality_columns ELSE '' END
	+ CASE WHEN INDEX_DETAILS.equality_columns IS NOT NULL AND INDEX_DETAILS.inequality_columns IS NOT NULL THEN ',' ELSE '' END
	+ CASE WHEN INDEX_DETAILS.inequality_columns IS NOT NULL THEN INDEX_DETAILS.inequality_columns ELSE '' END
	+ ')'
	+ CASE WHEN @useIncludeColumns = 1 AND INDEX_DETAILS.included_columns IS NOT NULL THEN ' INCLUDE (' + INDEX_DETAILS.included_columns + ')' ELSE '' END
	+ 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [' + @indexFileGroupName + ']'
	AS QUERY
FROM sys.dm_db_missing_index_groups AS INDEX_GROUPS
INNER JOIN sys.dm_db_missing_index_group_stats AS INDEX_GROUP_STATS ON
	INDEX_GROUP_STATS.group_handle = INDEX_GROUPS.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS INDEX_DETAILS ON
	INDEX_DETAILS.index_handle = INDEX_GROUPS.index_handle
INNER JOIN sys.tables AS TABS ON
	TABS.object_id = INDEX_DETAILS.object_id
WHERE INDEX_DETAILS.database_id = DB_ID(@databaseName)

DECLARE @rowCount AS INT
SELECT @rowCount = COUNT(*)
FROM @missingIndexCreationLogic

DECLARE @indexCount AS INT = @rowCount

DECLARE @query AS VARCHAR(MAX) = ''

WHILE @rowCount > 0
BEGIN
	SELECT TOP 1 @query = QUERY
	FROM @missingIndexCreationLogic
	ORDER BY QUERY
	
	DELETE
	FROM @missingIndexCreationLogic
	WHERE QUERY = @query
	
	EXEC(@query)

	SET @rowCount = @rowCount - 1
END

SELECT @indexCount